Kenya Geospatial Data Integration

Data Sources:

• Kenya Counties & Subcounties
• Kenya Counties, Constituencies & Wards
• Kenya County Assembly Boundaries (GeoJSON)

This directory contains the Kenya ward boundary data and scripts for integrating it into both PostGIS-enabled PostgreSQL and SQLite/SpatiaLite databases using Drizzle ORM.

Overview

Our Kenya dataset includes:

• 1,450+ electoral wards across all 47 counties
• MultiPolygon geometries representing ward boundaries with islands/complex shapes
• Administrative hierarchy: County → Constituency → Ward → Sub-county
• CRS84/EPSG:4326 coordinate system (standard lat/lng)

Database Implementations

This project supports two database implementations:

1. PostgreSQL/PostGIS - Full implementation with Drizzle ORM
2. SQLite/SpatiaLite - Alternative implementation with better-sqlite3

PostgreSQL/PostGIS Implementation

Located in src/drizzle-pg-postgis/ directory.

Features:

• Complete ward boundary data with geometry column
• Custom PostGIS types to work around Drizzle ORM limitations
• Spatial query functions (point-in-polygon, nearest neighbor, etc.)
• GiST indexes for optimized spatial queries

Source Files

• wards_geojson.ts - Complete ward boundary geometries in GeoJSON format
• sub_county.ts - County-to-subcounty mappings for administrative hierarchy
• insert_wards.ts - Data insertion script with geometry processing

Ward Properties

Each ward feature contains:

{
  id: string,           // Unique ward identifier
  wardcode: string,     // Official ward code
  ward: string,         // Ward name
  county: string,       // County name
  countycode: number,   // County code
  const: string,        // Constituency name
  constcode: number,    // Constituency code
  geometry: {           // MultiPolygon boundary
    type: "MultiPolygon",
    coordinates: number[][][][] // [polygon][ring][point][lng,lat]
  }
}

PostGIS Custom Types

Due to a known issue in Drizzle ORM, the built-in geometry() function incorrectly generates geometry(point) columns regardless of the specified type and ignores SRID configuration.

The Problem

// This generates incorrect SQL: geometry(point) instead of geometry(MultiPolygon, 4326)
geometry("geometry", { type: "multipolygon", srid: 4326 });

Our Solution: Custom Types

We created custom PostGIS types in src/lib/drizzle/postgis-types.ts:

import { customType } from "drizzle-orm/pg-core";

export const multiPolygon = customType<{
  data: string; // GeoJSON string
}>({
  dataType() {
    return "geometry(MultiPolygon, 4326)";
  },
});

export const point = customType<{
  data: { x: number; y: number } | string;
}>({
  dataType() {
    return "geometry(Point, 4326)";
  },
});

Schema Implementation

export const kenyaWards = pgTable(
  "kenya_wards",
  {
    id: serial("id").primaryKey(),
    wardCode: varchar("ward_code", { length: 10 }).notNull(),
    ward: text("ward").notNull(),
    county: text("county").notNull(),
    countyCode: integer("county_code").notNull(),
    subCounty: text("sub_county"),
    constituency: text("constituency").notNull(),
    constituencyCode: integer("constituency_code").notNull(),
    geometry: multiPolygon("geometry").notNull(), // Uses custom type
  },
  (t) => [
    index("kenya_wards_geometry_gix").using("gist", t.geometry), // Spatial index
  ]
);

SQLite/SpatiaLite Implementation

Located in src/sqlite-spatialite/ directory.

Features:

• Complete ward boundary data with geometry column
• Integration with SpatiaLite extension for spatial functions
• Spatial query functions similar to PostGIS implementation
• Indexed geometry column for optimized spatial queries

Seeded Database

A pre-populated SQLite database with all Kenya ward boundaries is available at:
src/data/kenya_wards.db

This database includes:

• All 1,450+ wards with their boundary geometries
• Properly indexed geometry column for spatial queries
• Administrative hierarchy information (county, constituency, etc.)

Source Files

• client.ts - Database initialization with SpatiaLite extension
• insert-wards.ts - Data insertion script
• query-wards.ts - Spatial query functions
• utils.ts - Utility functions for geometry handling

Data Processing Pipeline

1. GeoJSON Preparation

const geometry = {
  type: "MultiPolygon",
  coordinates: ward.coordinates, // Raw coordinates from geojson source // number[][][][] format
};

const processedWard = {
  ...ward,
  id: parseInt(ward.id, 10),
  countyCode: ward.countyCode ? ward.countyCode : -1,
  constituencyCode: ward.constituencyCode ? ward.constituencyCode : -1,
  subCounty: subCounty || "Unknown",
  // Store the geometry as GeoJSON text - will convert to PostGIS geometry in insert
  geometry: JSON.stringify(geometry),
};

2. Sub-county Mapping

const getSubCounty = (ward: string): string | null => {
  const subCounty = SUB_COUNTY_MAPPINGS.find((item) => {
    const subCounties = Object.values(item.sub_counties).flat();
    return subCounties.some((subCountyWard) =>
      subCountyWard.toLowerCase().includes(ward.toLowerCase())
    );
  });
  return subCounty?.county_name || null;
};

3. Database Insertion

// Simple batch insert - custom types handle PostGIS conversion
await db.insert(kenyaWards).values(wardsWithSubCounties);

Spatial Queries

We provide comprehensive spatial query functions in both implementations:

Point-in-Polygon (Exact Match)

/**
 * Find the ward that contains a given point (lat, lng)
 * This is the most accurate method - checks if the point is actually inside the ward boundary
 */
export async function findWardByPoint(latitude: number, longitude: number) {
  const point = sql`ST_SetSRID(ST_MakePoint(${longitude}, ${latitude}), 4326)`;

  const result = await db
    .select({
      ...getTableColumns(kenyaWards),
    })
    .from(kenyaWards)
    .where(sql`ST_Contains(${kenyaWards.geometry}, ${point})`)
    .limit(1);

  return result[0] || null;
}
// Find the ward containing specific coordinates
const ward = await findWardByPoint(-1.2921, 36.8219); // Nairobi

Nearest Neighbor

// Find closest ward by distance
const nearestWard = await findNearestWard(-1.2921, 36.8219);

Smart Search (Recommended)

// Try exact match first, fallback to nearest
const ward = await findWardSmart(-1.2921, 36.8219);

Distance-based Queries

// Find all wards within 5km
const nearbyWards = await findWardsWithinDistance(-1.2921, 36.8219, 5000);

Key Spatial Functions Used

PostGIS (PostgreSQL)

• ST_Contains(ward_geometry, point) - Point-in-polygon test
• ST_Distance(ward_geometry, point) - Calculate distance
• geometry <-> point - Fast distance operator for ordering
• ST_DWithin(geometry, point, distance) - Distance filtering
• ST_GeomFromGeoJSON(json) - Convert GeoJSON to PostGIS geometry
• ST_SetSRID(geometry, 4326) - Set coordinate reference system

SpatiaLite (SQLite)

• ST_Contains(ward_geometry, point) - Point-in-polygon test
• ST_Distance(ward_geometry, point) - Calculate distance
• ST_DWithin(ward_geometry, point, distance) - Distance filtering
• ST_GeomFromGeoJSON(json) - Convert GeoJSON to SpatiaLite geometry
• ST_SetSRID(geometry, 4326) - Set coordinate reference system

Usage Examples

Find Ward by Coordinates

import { findWardSmart } from "@/lib/drizzle/ward-queries";

// Example: Find ward containing Kenyatta University
const ward = await findWardSmart(-1.1677, 37.0162);
console.log(`${ward?.ward}, ${ward?.constituency}, ${ward?.county}`);

Property Geocoding

// When storing property locations
const property = {
  latitude: -1.2921,
  longitude: 36.8219,
  locationGeom: { x: 36.8219, y: -1.2921 }, // Point geometry
};

// Find administrative context
const ward = await findWardSmart(property.latitude, property.longitude);

Database Schema

Prerequisites

PostgreSQL/PostGIS

1. PostGIS Extension: CREATE EXTENSION IF NOT EXISTS postgis;
2. UUID Extension: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; (if using uuidgeneratev7)

SQLite/SpatiaLite

1. SpatiaLite Extension: Must be installed and accessible to better-sqlite3
2. Database file will be created at src/data/kenya_wards.db if it doesn't exist

Indexes

• Spatial Index: GiST index on geometry column for fast spatial queries
• Text Indexes: Consider adding indexes on ward, county, constituency for text searches

Migration Notes

• Custom types generate correct PostGIS column definitions
• Spatial indexes are automatically created via Drizzle schema
• No manual PostGIS function wrapping needed in queries thanks to custom types

Performance Considerations

1. Spatial Indexing: GiST indexes provide O(log n) spatial query performance
2. Coordinate Order: Both PostGIS and SpatiaLite use (longitude, latitude) internally
3. SRID Consistency: All geometries use EPSG:4326 for compatibility
4. Batch Operations: Use db.insert().values() for bulk ward insertions

Troubleshooting

Common Issues

1. "Geometry type mismatch": Ensure custom types are used instead of built-in geometry()
2. "Invalid geometry": Source data may need ST_MakeValid() processing
3. "No SRID": Custom types automatically set SRID=4326
4. SpatiaLite extension errors: Ensure the mod_spatialite extension is properly installed

Validation Queries

-- Check geometry validity
SELECT ward, ST_IsValid(geometry), ST_IsValidReason(geometry)
FROM kenya_wards WHERE NOT ST_IsValid(geometry);

-- Verify SRID and type
SELECT ward, ST_SRID(geometry), GeometryType(geometry)
FROM kenya_wards LIMIT 5;